package com.info.zhiduoduo.repository.mapper.maker;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.info.zhiduoduo.repository.dto.account.NotUpStandardDetailDto;
import com.info.zhiduoduo.repository.dto.account.NotUpStandardMakerDto;
import com.info.zhiduoduo.repository.dto.account.NotUpStandardMakerExcelDto;
import com.info.zhiduoduo.repository.dto.crowdsource.ExportProjectTemplateDto;
import com.info.zhiduoduo.repository.dto.evaluate.EvaluateNoticeInfoDto;
import com.info.zhiduoduo.repository.dto.maker.*;
import com.info.zhiduoduo.repository.dto.project.ExportZddProjectTemplateDto;
import com.info.zhiduoduo.repository.dto.project.UserForPublishProjectDto;
import com.info.zhiduoduo.repository.entity.maker.Maker;
import com.info.zhiduoduo.repository.entity.maker.MakerStudio;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author TerryJ
 * @since 2018-12-08
 */
public interface MakerMapper extends BaseMapper<Maker> {

    @Select("select * from zdd_maker where phone = #{phone}")
    Maker checkUserExistByPhone(@Param("phone") String phone);

    @Select(" select u.userid,u.nickname,u.avatar,u.phone,u.state,ua.realname_auth as authState," +
		    " if(IFNULL(ub.acctno,'')='',0,1) as hasBankcard," +
		    " (select ar.reason from zdd_maker_auth_record ar " +
		    " where ar.acctid = ua.acctid order by ar.id desc limit 1) as authReason" +
		    " from zdd_maker u " +
		    " left join zdd_maker_account ua on ua.userid = u.userid " +
		    " left join zdd_maker_bankcard ub on ub.userid = u.userid and ub.state = 1 and ub.accttype = 1 " +
		    " where openid = #{openid}")
    Maker getUserByOpenid(@Param("openid") String openid);

    @Select(" select u.userid,u.nickname,u.avatar,u.phone,u.state,ua.realname_auth as authState," +
		    " if(IFNULL(ub.acctno,'')='',0,1) as hasBankcard," +
		    " (select ar.reason from zdd_maker_auth_record ar " +
		    " where ar.acctid = ua.acctid order by ar.id desc limit 1) as authReason" +
		    " from zdd_maker u " +
		    " left join zdd_maker_account ua on ua.userid = u.userid " +
		    " left join zdd_maker_bankcard ub on ub.userid = u.userid and ub.state = 1 and ub.accttype = 1 " +
		    " where taxer_openid = #{taxerOpenid}")
    Maker getUserByTaxerOpenid(@Param("taxerOpenid") String taxerOpenid);

    @Update("update zdd_maker set phone=#{phone} where userid = #{userid}")
    int updatePhoneByUserid(@Param("userid") int userid, @Param("phone") String phone);

    @Select("select zu.userid,zus.username,zus.studionickname,zu.nickname,zu.avatar,zu.phone,zus.bizscope,zus.invoicescope " +
		    " from zdd_maker zu " +
		    " JOIN zdd_maker_account ma on ma.userid = zu.userid " +
		    " JOIN zdd_maker_studio zus on zus.userid = zu.userid AND zus.state = 2 " +
            " JOIN zdd_system_tradecategory zst ON zus.tradecategoryid = zst.tradecategoryid " +
            " where ${whereStr} ${orderStr}")
    List<UserForPublishProjectDto> queryUserForPublishProject(@Param("whereStr") String whereStr, @Param("orderStr") String orderStr);

	@Select(" select u.*,tp.state as paidState,ua.acctid,ua.realname_auth realnameAuth,ua.realname,ua.identity_type,ua.idno,ua.opening_location, " +
			" ua.idfrontimg,ua.idbackimg,ua.educationimg,ua.identificationimg,ua.agree_protocol," +
			" ua.business_data_submint,ua.examine_business_data_state,ua.reject_business_register_reason," +
			" ua.business_authentication_state,ua.business_authentication_img,ua.business_authentication_reason,ua.core_module, " +
			" ua.gong_qing_data_submint,ua.government_examine_state,ua.government_rejection_reasons,ua.electronic_signature,ua.temporary_login, " +
			" us.username,studionickname,intro,us.creditcode,addr,studiophone,us.fieldid,c.compname," +
			" sf.fieldname,us.tradecategoryid,us.bizscope,us.specialinvoice,us.invoicescope,us.studiolicensephoto," +
			" us.studiotaxphoto,us.specialinvoice,us.state studioState,us.brokerage_compute_state," +
			" us.taxer,us.taxer_paid,us.taxertime,us.tax_acct_state,us.open_tax_acct,tp.state as paidState," +
			" us.crowdsource_convert_zdd_maker,us.fixed_service,us.fixed_service_rate,us.quota_flag," +
			" us.twelve_month_quota_flag,us.entrust_tax_video_file,us.entrust_tax_video_state,us.entrust_tax_video_remark," +
			" ub.payername,ub.cardtype,ub.bankname,ub.telephone,ub.acctno,ub.idno as acctIdno,ub.open_acct_bank_name," +
			" ub2.payername as payername2,ub2.cardtype as cardtype2,ub2.bankname bankname2,ub2.bankno bankno2," +
			" ub2.telephone as telephone2,ub2.acctno as acctno2,ub2.idno as acctIdno2," +
			" ub2.open_acct_bank_name as openAcctBankName2,ub2.province as bankProvince2,ub2.city as bankCity2," +
			" pa.realname as inviter,ua.education,ua.political,ua.email,ua.residence,ua.industry,ua.occupation,ua.name_of_emergency_contact," +
			" ua.phone_of_emergency_contact,ub.alipay_account,ub.payment_account_status,ub2.payment_account_status AS paymentAccountStatusTwo   " +
			" from zdd_maker u " +
			" left join zdd_maker_account ua on ua.userid = u.userid " +
			" left join zdd_maker_bankcard ub on ub.userid = u.userid and ub.state = 1 and ub.accttype = 1 " +
			" left join zdd_maker_bankcard ub2 on ub2.userid = u.userid and ub2.state = 1 and ub2.accttype = 2 " +
			" left join zdd_maker_studio us on us.userid = u.userid " +
			" left join zdd_studio_field sf on sf.fieldid = us.fieldid " +
			" left join zdd_company c on c.compid = u.bindcompid " +
			" left join zdd_partner_account pa on pa.promo_code = u.invite_code and u.invite_code != '' " +
			" left join zdd_taxer_payment tp on tp.userid = u.userid and tp.type = 1 and tp.deleted = 0 " +
			" where u.userid = #{userid}")
	MakerDetailDto getUserDetail(@Param("userid") int userid);

	// 用户校验创客获取一些基础信息
	@Select(" select u.userid,u.phone,u.nickname,u.openid,u.usertype,u.maker_type,u.bindcompid,u.state,u.createtime," +
			" us.username,ua.acctid,ua.realname,ua.realname_auth realnameAuth,ua.identity_type,ua.idno,ua.opening_location, " +
			" ua.idfrontimg,ua.idbackimg,ua.identificationimg,ua.temporary_login,studionickname,us.creditcode,studiophone,c.compname," +
			" us.state studioState,us.taxer,us.taxer_paid,us.taxertime,us.tax_acct_state,us.open_tax_acct," +
			" ub.payername,ub.acctno,ub.idno as acctIdno,ub.alipay_account,us.invoicescope " +
			" from zdd_maker u " +
			" left join zdd_maker_account ua on ua.userid = u.userid " +
			" left join zdd_maker_studio us on us.userid = u.userid " +
			" left join zdd_maker_bankcard ub on ub.userid = u.userid and ub.state = 1 and ub.accttype = 1 " +
			" left join zdd_company c on c.compid = u.bindcompid " +
			" where u.userid = #{userid}")
	MakerDetailDto getUserData(@Param("userid") int userid);

	@Select(" select u.userid,u.avatar,us.username,us.studionickname,ua.realname,us.intro," +
			" us.specialinvoice,us.invoicescope,us.fieldid,sf.fieldname,ua.opening_location " +
			" from zdd_maker u " +
			" join zdd_maker_studio us on us.userid = u.userid " +
			" join zdd_maker_account ua on ua.userid = u.userid " +
			" left join zdd_studio_field sf on sf.fieldid = us.fieldid " +
			" where CONCAT(u.nickname,us.username,us.studionickname,ua.realname) like '%${username}%' " +
			" and ua.realname_auth = 5 " +
			" and us.state = 2 " +
			" and if(ua.opening_location = 1, us.tax_acct_state = 3, 1=1)" +
			" and if(#{fieldid}=0, 1=1, us.fieldid = #{fieldid} ) " +
			" and u.maker_type = 1 and u.state != 4 ")
	List<MakerIntroDto> queryUserByField(@Param("username") String username, @Param("fieldid") int fieldid);

	@Delete(" DELETE u,ua,us,uaf,uatt,ub,uar,uc,ut " +
			" FROM zdd_maker u " +
			" LEFT JOIN zdd_maker_account ua ON ua.userid = u.userid " +
			" LEFT JOIN zdd_maker_studio us ON us.userid = u.userid " +
			" LEFT JOIN zdd_maker_accountflow uaf ON uaf.userid = u.userid " +
			" LEFT JOIN zdd_maker_attachment uatt ON uatt.userid = u.userid " +
			" LEFT JOIN zdd_maker_bankcard ub ON ub.userid = u.userid " +
			" LEFT JOIN zdd_maker_auth_record uar ON uar.acctid = ua.acctid " +
			" LEFT JOIN zdd_maker_case uc ON uc.userid = u.userid " +
			" LEFT JOIN zdd_maker_tag ut ON ut.userid = u.userid " +
			" WHERE u.userid = #{userid}")
	int deleteUser(@Param("userid") Integer userid);

	@Select(" select CASE m.maker_type WHEN 2 THEN m.taxer_openid ELSE m.openid END as openid " +
			" from zdd_maker m " +
			" join zdd_maker_account ma on ma.userid = m.userid " +
			" where m.state in (2,4) and ma.realname_auth IN ( 4, 5 ) " +
			" and if(#{makerType} = 0, 1=1, m.maker_type = #{makerType}) " +
			" and m.notice_flag = 0 " +
			" and if(#{openingLocation} = 0, 1=1, ma.opening_location = #{openingLocation}) ")
	List<String> getAllValidOpenids(int makerType, int openingLocation);

	/**
	 * 查询评价公告所需的数据
	 * @param makerType
	 * @return
	 */
	@Select(" select m.userid AS userId," +
			" CASE m.maker_type WHEN 2 THEN m.taxer_openid ELSE m.openid END AS openId, " +
			" CASE WHEN m.maker_type = 2 THEN 5 WHEN m.maker_type = 3 THEN 6 " +
			" ELSE 1 END AS userType " +
			" from zdd_maker m " +
			" join zdd_maker_account ma on ma.userid = m.userid " +
			" where m.state in (2,4) and ma.realname_auth IN ( 4, 5 ) " +
			" and if(#{makerType} = 0, 1=1, m.maker_type = #{makerType})")
	List<EvaluateNoticeInfoDto> getPushEvaluateNoticeNeedData(int makerType);

	/**
	 * 根据地区查询openid
	 * @param residence 地区编号
	 * @param flag 1 某个省市  1 整个省
	 * @param  makerType 1  创客  2 智杰祱创客
	 * @return String
	 */
	@Select(" select " +
			" if(#{makerType} = 2,m.taxer_openid,m.openid) as openid " +
			" from zdd_maker m " +
			" join zdd_maker_account ma on ma.userid = m.userid " +
			" where m.state = 2 and ma.realname_auth IN ( 4, 5 ) " +
			" and m.maker_type = #{makerType} and m.notice_flag = 0 " +
			" and if(#{flag} = 1, ma.residence = #{residence}, ma.residence like '%${residence}%') " +
			" and if(#{openingLocation} = 0, 1=1, ma.opening_location = #{openingLocation}) ")
	List<String> getAllValidOpenidsByResidence(String residence, int flag, int makerType, int openingLocation);

	@Select(" select m.userid from zdd_maker m " +
			" join zdd_maker_account ma on ma.userid = m.userid " +
			" where m.state in (2,3,4) and ma.realname_auth IN(4,5)")
	List<Integer> getValidMakerIds();

	@Select(" select ma.userid,ma.realname,m.state from zdd_maker_account  ma " +
			" JOIN zdd_maker m ON m.userid = ma.userid" +
			" where ma.idno = #{idno} order by ma.userid desc limit 1 ")
	FindMakerDto findUseridByIdno(String idno);

	@Select(" select ma.userid from zdd_maker_account ma " +
			" join zdd_maker m on m.userid = ma.userid " +
			" where ma.idno = #{idno} order by m.userid desc limit 1 ")
	Integer findValidUseridByIdno(String idno);

	@Select(" SELECT  cr.userid  ,  ms.username   , ms.open_finishtime  ,ma.realname , c.compname , m.maker_type , ma.opening_location , " +
			" ( SELECT m.business_manager FROM zdd_business_manager m LEFT JOIN zdd_company_account a ON a.business_manager_id = m.business_manager_id  " +
			"   WHERE a.compid = m.bindcompid ) as manager , " +
			" ( SELECT COUNT( id ) FROM zdd_maker_cycle_record WHERE userid = cr.userid AND state = 0 ) AS pending , " +
			" ( SELECT COUNT(id) FROM zdd_maker_handle where userid = cr.userid ) as processed,ms.specialinvoice,cr.special_handling,m.bindcompid " +
			" FROM zdd_maker_cycle_record cr  " +
			" LEFT JOIN zdd_maker m ON m.userid = cr.userid  " +
			" LEFT JOIN zdd_maker_studio ms ON ms.userid = cr.userid  " +
			" LEFT JOIN zdd_maker_account ma ON m.userid = cr.userid  " +
			" LEFT JOIN zdd_company c ON c.compid = m.bindcompid  " +
			" LEFT JOIN zdd_maker_handle h ON ( h.userid = cr.userid AND h.handle_year = cr.year_mark )  " +
			" where cr.userid = ma.userid  " +
			" AND m.state != 4  " +
			" and cr.total_amount < 100000  " +
			" and if(#{special}=0,1=1,ms.specialinvoice=1) " +
			" AND cr.state = #{state}  " +
			" and if(ISNULL(#{name}) OR #{name} = '', 1 = 1  , CONCAT(ma.realname,c.compname,ms.username) like '%${name}%') "+
			" GROUP BY cr.userid " +
			" having IF(#{state} = 0,pending > 0,pending = 0) " +
			" order by ms.open_finishtime desc")
	List<NotUpStandardMakerDto> notUpStandardMakerList(@Param("name") String name ,
													   @Param("state") int state,
													   @Param("special")Integer special);

	@Select(" SELECT m.userid AS userId, @i := @i + 1 AS row, ma.realname AS name, mb.acctno, ma.idno " +
			" FROM zdd_maker m " +
			" LEFT JOIN zdd_maker_account ma ON ma.userid = m.userid " +
			" LEFT JOIN zdd_maker_bankcard mb on mb.userid = m.userid and mb.state = 1 and mb.accttype = 1,  " +
			" ( SELECT @i := 0 ) r " +
			" WHERE m.state = 2 and ma.realname_auth = 5 AND m.bindcompid = #{companyId} AND m.state != 4 ")
	List<ExportZddProjectTemplateDto> queryValidCompanyMaker(@Param("companyId") int companyId);
	@Select(" SELECT m.userid AS userId, @i := @i + 1 AS row, ma.realname AS name, ma.idno,mb.alipay_account acctno " +
			" FROM zdd_maker m " +
			" LEFT JOIN zdd_maker_account ma ON ma.userid = m.userid " +
			" LEFT JOIN zdd_maker_bankcard mb on mb.userid = m.userid and mb.state = 1 and mb.accttype = 1, " +
			" ( SELECT @i := 0 ) r " +
			" WHERE m.state = 2 and ma.realname_auth = 5 AND m.bindcompid = #{companyId} AND m.state != 4  ")
	List<ExportZddProjectTemplateDto> queryValidCompanyMakerForAliPay(@Param("companyId") int companyId);

	@Select(" SELECT m.userid AS userId, @i := @i + 1 AS row, ma.realname AS name, mb.acctno, ma.idno " +
			" FROM zdd_maker m " +
			" LEFT JOIN zdd_maker_account ma ON ma.userid = m.userid " +
			" LEFT JOIN zdd_maker_bankcard mb on mb.userid = m.userid and mb.state = 1 and mb.accttype = 1, " +
			" ( SELECT @i := 0 ) r " +
			" WHERE ma.realname_auth = 5 AND m.bindcompid = #{companyId} AND m.state != 4 ")
	List<ExportProjectTemplateDto> queryCompanyMaker(@Param("companyId") int companyId);

	@Select(" SELECT m.userid AS userId, @i := @i + 1 AS row, ma.realname AS name, mb.alipay_account acctno , ma.idno " +
			" FROM zdd_maker m " +
			" LEFT JOIN zdd_maker_account ma ON ma.userid = m.userid " +
			" LEFT JOIN zdd_maker_bankcard mb on mb.userid = m.userid and mb.state = 1 and mb.accttype = 1, " +
			" ( SELECT @i := 0 ) r " +
			" WHERE ma.realname_auth = 5 AND m.bindcompid = #{companyId} AND m.state != 4 AND mb.state = 1 ")
	List<ExportProjectTemplateDto> queryCompanyMakerForAliPay(@Param("companyId") int companyId);



	// 查询 创客 周期内（12月） 交易金额
	@Select(" SELECT  sum( uaf.flow_amount )  AS totalAmount  FROM zdd_maker_accountflow uaf " +
			" LEFT JOIN zdd_maker_studio ms ON uaf.userid = ms.userid " +
			" WHERE uaf.amounttype = 0  " +
			" and  uaf.createtime >= DATE_ADD(ms.open_finishtime, INTERVAL #{year}-1 YEAR) and   uaf.createtime <= DATE_ADD(ms.open_finishtime, INTERVAL #{year} YEAR) " +
			" AND uaf.userId = #{userId}  ")
	BigDecimal selectMakerCycleAmount(@Param("userId") int userId , @Param("year") int year);



	@Select(" SELECT cr.userid , cr.total_amount as totalAmount , cr.year_mark as yearMark , cr.year_cycle as yearCycle , cr.state , " +
			" mh.handle_type as handleType ,mh.handle_amount, mh.filepath , mh.remark  " +
			" FROM zdd_maker_cycle_record cr " +
			" LEFT JOIN zdd_maker_handle mh ON ( mh.userid = cr.userid AND mh.handle_year = cr.year_mark ) " +
			" where cr.userid = #{userid} ")
	List<NotUpStandardDetailDto> notUpStandardMakerDetail(Integer userid);

	@Select(" SELECT  cr.total_amount as totalAmount " +
			" FROM zdd_maker_cycle_record cr " +
			" LEFT JOIN zdd_maker_handle mh ON ( mh.userid = cr.userid AND mh.handle_year = cr.year_mark ) " +
			" where cr.userid = #{userid} " +
			" order by cr.year_mark desc limit 1  ")
	BigDecimal notUpStandardMakerDetailExcel(Integer userid);

	@Select(" SELECT  cr.userid  ,  ms.username   , ms.open_finishtime  ,ma.realname , c.compname , m.maker_type , ma.opening_location , " +
			" ( SELECT m.business_manager FROM zdd_business_manager m LEFT JOIN zdd_company_account a ON a.business_manager_id = m.business_manager_id  " +
			"   WHERE a.compid = m.bindcompid ) as manager , " +
			" ( SELECT COUNT( id ) FROM zdd_maker_cycle_record WHERE userid = cr.userid AND state = 0 ) AS pending , " +
			" ( SELECT COUNT(id) FROM zdd_maker_handle where userid = cr.userid ) as processed,ms.specialinvoice,cr.special_handling,m.bindcompid " +
			" FROM zdd_maker_cycle_record cr  " +
			" LEFT JOIN zdd_maker m ON m.userid = cr.userid  " +
			" LEFT JOIN zdd_maker_studio ms ON ms.userid = cr.userid  " +
			" LEFT JOIN zdd_maker_account ma ON m.userid = cr.userid  " +
			" LEFT JOIN zdd_company c ON c.compid = m.bindcompid  " +
			" LEFT JOIN zdd_maker_handle h ON ( h.userid = cr.userid AND h.handle_year = cr.year_mark )  " +
			" where cr.userid = ma.userid  " +
			" AND m.state != 4  " +
			" and cr.total_amount < 100000  " +
			" and if(#{special}=0,1=1,ms.specialinvoice=1) " +
			" AND cr.state = #{state}  " +
			" and if(ISNULL(#{name}) OR #{name} = '', 1 = 1  , CONCAT(ma.realname,c.compname,ms.username) like '%${name}%') "+
			" GROUP BY cr.userid " +
			" having IF(#{state} = 0,pending > 0,pending = 0) " +
			" order by ms.open_finishtime desc")
	List<NotUpStandardMakerExcelDto> notUpStandardMakerExcelList(@Param("name") String name ,
																 @Param("state") int state,
																 @Param("special")Integer special);
	@Select(" SELECT * from zdd_maker_studio us where state = 2 and fixed_service = 0 ${standardRate}")
	List<MakerStudio> getStandardServiceRateList(@Param("standardRate") String standardRate);


	@Select(" select  acc.userid,acc.realname,stu.username  from zdd_maker_account acc " +
			" left join zdd_maker_studio stu on acc.userid=stu.userid " +
			" where acc.realname like concat('%',#{name},'%') or stu.username like concat('%',#{name},'%') " +
			" order by acc.createtime desc ")
	 List<QueryMakerNameDto> queryMakerDto(@Param("name")String name);

	@Select(" select m.userid,m.bindcompid,m.createtime,c.compname,c.friendly_state,ma.realname,ma.is_registered_studio,ma.maker_label,ms.username,ms.invoicescope," +
			" CASE WHEN ali.userid is null THEN 0 ELSE 1 END as isOpenAliPay," +
			" CASE " +
			"   WHEN ms.taxer = 1  AND m.maker_type != 2 THEN 4 " +
			"   ELSE m.maker_type " +
			" END as makerType," +
			" CASE " +
			"   WHEN m.state = 4 THEN 3 " +
			"   WHEN ms.open_tax_acct = 1 THEN 5 " +
			"   WHEN ms.state = 2 THEN 4 " +
			"   WHEN ma.realname_auth = 4 THEN 2 " +
			"   ELSE 1 " +
			" END as unifiedState," +
			" ma.temporary_login as temporaryLogin," +
			" IF(m.openid != '' or m.taxer_openid != '' or m.crowdsource_openid != '', 1, 0) as voluntary," +
			" ma.expert_label_state as expertLabelState," +
			" CASE WHEN pa.studio_id > 0 THEN 1 ELSE 0 END as partnerStudio," +
			" ma.opening_location as openingLocation," +
			" CASE WHEN ub2.acctno is null THEN 0 ELSE 1 END as isOpenAccNo2," +
			" case when zca.acctid is null then 0 else 1 end as naturalPersonStatus "+
			" from zdd_maker m" +
			" join zdd_maker_account ma on ma.userid = m.userid " +
			" join zdd_maker_studio ms on ms.userid = m.userid " +
			" left join zdd_company c on m.bindcompid = c.compid " +
			" left join zdd_maker_alipay ali on m.userid = ali.userid and ali.deleted=0 " +
			" left join zdd_partner_account pa on pa.studio_id = m.userid " +
			" left join zdd_crowdsource_account zca on zca.idno = ma.idno"+
			" left join zdd_maker_bankcard ub2 on ub2.userid = ms.userid and ub2.state = 1 and ub2.accttype = 2 "+
			" where m.maker_type != 4 " +
			" and m.state != 4 " +
			" and ma.maker_label like '%1%' " +
			" and c.compname like '%${compname}%' " +
			" and CONCAT(ma.realname, ms.username) like '%${name}%' " +
			" and if(isnull(#{friendlyState}),1=1,c.friendly_state=#{friendlyState}) " +
			" and ms.invoicescope like '%${invoicescope}%' " +
			" and if(isnull(#{isOpenAliPay}), 1=1, if(#{isOpenAliPay}=1, ali.userid is not null, ali.userid is null)) " +
			" order by m.createtime desc")
	List<FriendlyMakerDto> queryFriendlyMakerList(String compname, String name, Integer friendlyState, String invoicescope,Integer isOpenAliPay);

	/**
	 * 提交项目前，查询创客状态（限制天津未开户创客、限制已注销或者申请注销已受理的创客交易）
	 * @param userid
	 * @return
	 */
	@Select(" SELECT u.userid,u.state,ua.realname_auth,ua.opening_location,us.tax_acct_state,ura.state as rescissionState,ua.temporary_login ,ub2.alipay_account,us.username,ua.realname" +
			" from zdd_maker u " +
			" left join zdd_maker_account ua on ua.userid = u.userid " +
			" left join zdd_maker_studio us on us.userid = u.userid " +
			" left join (select id,state,userid from zdd_maker_rescission_apply where userid = #{userid} ORDER BY id desc limit 1) ura on ura.userid = u.userid " +
			" left join zdd_maker_bankcard ub2 on ub2.userid = u.userid and ub2.state = 1" +
			" where u.userid = #{userid} " +
			" group by u.userid")
	MakerDetailDto getUserState(@Param("userid") int userid);

	@Select("<script>" +
			" select ma.createtime registerDate,m.userid,ma.realname,m.phone,m.nickname,ms.username,m.bindcompid," +
			" c.compname bindcompname,ma.temporary_login," +
			" IF(m.openid != '' or m.taxer_openid != '' or m.crowdsource_openid != '', 1, 0) as voluntary,ma.expert_label_state," +
			" IF(ma.maker_label like '%1%', 1, 0) as friendlyMaker,c.friendly_state friendlyCompany," +
			" CASE WHEN mal.userid > 0 THEN 1 ELSE 0 END as isOpenAlipay," +
			" CASE WHEN pa.studio_id > 0 THEN 1 ELSE 0 END as partnerStudio," +
			" CASE " +
			"   WHEN m.state = 4 THEN 3 " +
			"   WHEN ms.open_tax_acct = 1 THEN 5 " +
			"   WHEN ms.state = 2 THEN 4 " +
			"   WHEN ma.realname_auth = 4 THEN 2 " +
			"   ELSE 1 " +
			" END as unifiedState," +
			" CASE " +
			"   WHEN ms.taxer = 1  AND m.maker_type != 2 THEN 4 " +
			"   ELSE m.maker_type " +
			" END as makerType," +
			" ma.opening_location,mrat.finishtime as rescissionDate,ms.open_finishtime as openStudioDate," +
			" ms.open_tax_acct_finishtime as openAcctDate,m.state,ma.realname_auth,ms.state as studioState,ms.tax_acct_state," +
			" CASE WHEN ub2.acctno is null THEN 0 ELSE 1 END as isOpenAccNo2," +
			" ms.open_tax_acct,ms.taxer " +
			" from zdd_maker m " +
			" join zdd_maker_account ma on ma.userid = m.userid " +
			" left join zdd_maker_studio ms on ms.userid = m.userid " +
			" left join zdd_company c on c.compid = m.bindcompid " +
			" left join zdd_maker_alipay mal on mal.userid = m.userid and mal.deleted=0 " +
			" left join zdd_partner_account pa on pa.studio_id = m.userid " +
			" left join zdd_maker_bankcard ub2 on ub2.userid = ms.userid and ub2.state = 1 and ub2.accttype = 2 "+
			" left join (SELECT mra.* FROM zdd_maker_rescission_apply mra,(select max(id) as id,userid " +
			"   from zdd_maker_rescission_apply GROUP BY userid ) t where mra.id = t.id ) mrat on mrat.userid = m.userid " +
			" <where>" +
			"   <if test='from != null'>ma.createtime &gt;= #{from}</if>" +
			"   <if test='to != null'>AND ma.createtime &lt;= #{to}</if>" +
			"   <if test=\"name != null and name != ''\">" +
			"       AND CONCAT(ma.realname,IFNULL(ms.username, '')) like CONCAT('%', #{name}, '%') " +
			"   </if>" +
			"   <if test=\"phone != null and phone != ''\">" +
			"       AND m.phone like CONCAT('%', #{phone}, '%')" +
			"   </if>" +
			"   <if test=\"bindcompname != null and bindcompname != ''\">" +
			"       AND c.compname like CONCAT('%', #{bindcompname}, '%')" +
			"   </if>" +
			"   <choose>" +
			"       <when test= 'unifiedState == 1'>AND ma.realname_auth = 2</when>" +
			"       <when test= 'unifiedState == 2'>AND ma.realname_auth = 4 AND m.state = 2</when>" +
			"       <when test= 'unifiedState == 3'>AND m.state = 4</when>" +
			"       <when test= 'unifiedState == 4'>AND ms.state = 2 and ms.open_tax_acct = 0 AND m.state = 2</when>" +
			"       <when test= 'unifiedState == 5'>AND ms.open_tax_acct = 1 AND m.state = 2</when>" +
			"   </choose>" +
			"   <choose>" +
			"       <when test= 'makerType == 1'>AND m.maker_type = 1</when>" +
			"       <when test= 'makerType == 2'>AND m.maker_type = 2</when>" +
			"       <when test= 'makerType == 3'>AND m.maker_type = 3</when>" +
			"       <when test= 'makerType == 4'>AND ms.taxer = 1 AND m.maker_type != 2</when>" +
			"   </choose>" +
			"   <if test='temporaryLogin != null'>AND ma.temporary_login = #{temporaryLogin}</if>" +
			"   <choose>" +
			"       <when test= 'voluntary == 0'>AND (m.openid = '' and m.taxer_openid = '' and m.crowdsource_openid = '')</when>" +
			"       <when test= 'voluntary == 1'>AND (m.openid != '' or m.taxer_openid != '' or m.crowdsource_openid != '')</when>" +
			"   </choose>" +
			"   <if test='expertLabelState != null'>AND ma.expert_label_state = #{expertLabelState}</if>" +
			"   <choose>" +
			"       <when test= 'friendlyMaker == 0'>AND NOT ma.maker_label like '%1%'</when>" +
			"       <when test= 'friendlyMaker == 1'>AND ma.maker_label like '%1%'</when>" +
			"   </choose>" +
			"   <if test='friendlyCompany != null'>AND c.friendly_state = #{friendlyCompany} </if>" +
			"   <choose>" +
			"       <when test= 'isOpenAlipay == 0'>AND ISNULL(mal.userid)</when>" +
			"       <when test= 'isOpenAlipay == 1'>AND mal.userid &gt; 0</when>" +
			"   </choose>" +
			"   <choose>" +
			"       <when test= 'partnerStudio == 0'>AND ISNULL(pa.studio_id)</when>" +
			"       <when test= 'partnerStudio == 1'>AND pa.studio_id &gt; 0</when>" +
			"   </choose>" +
			"   <if test='openingLocation != null'>AND ma.opening_location = #{openingLocation} </if>" +
			" </where>" +
			" having IF(isnull(#{isOpenAccNo2}),1=1,isOpenAccNo2 = #{isOpenAccNo2})" +
			"</script>")
	List<MakerStatusInfoDto> listMakers(@Param("from") Date from, @Param("to") Date to, @Param("name") String name, @Param("phone") String phone,
	                  @Param("bindcompname") String bindcompname, @Param("unifiedState") Integer unifiedState,
	                  @Param("makerType") Integer makerType, @Param("temporaryLogin") Integer temporaryLogin,
	                  @Param("voluntary") Integer voluntary, @Param("expertLabelState") Integer expertLabelState,
	                  @Param("friendlyMaker") Integer friendlyMaker, @Param("friendlyCompany") Integer friendlyCompany,
	                  @Param("isOpenAlipay") Integer isOpenAlipay, @Param("partnerStudio") Integer partnerStudio,
	                  @Param("openingLocation") Integer openingLocation,
					  @Param("isOpenAccNo2")Integer isOpenAccNo2);

	@Select(" SELECT m.special_rate_flag AS MakerSpecialRateFlag, " +
			" c.special_rate_flag AS CompanySpecialRateFlag " +
			" FROM zdd_maker m " +
			" LEFT JOIN zdd_company c ON c.compid = m.bindcompid " +
			" WHERE m.userid = #{userid}")
	SpecialRatesDto specialRates(@Param("userid") int userid);


	@Update("update zdd_maker set platform_hosting = #{PlatformHosting}  WHERE userid = #{userid}")
	int emptyCustodianId(@Param("userid") int userid,@Param("PlatformHosting") int PlatformHosting);

	@Select(" SELECT " +
			"  ma.userid, " +
			"  ma.realname, " +
			"  m.state, " +
			"  ma.idno, " +
			"  ms.username " +
			" FROM " +
			"  zdd_maker_account ma " +
			"  LEFT JOIN zdd_maker m ON m.userid = ma.userid  " +
			"  LEFT JOIN zdd_maker_studio ms ON ms.userid = ma.userid " +
			" WHERE " +
			"  (ma.realname = #{name} or ms.username like '%${name}%' ) " +
			"  AND m.state = 2 " +
			"  AND m.maker_type != 2 " +
			"  AND ma.realname_auth = 5" +
			"  order by ma.userid desc ")
	List<FindMakerByNameDto> findMakersByName(@Param("name") String name);

	//获取今日生日的创客
	@Select("SELECT m.phone" +
			" FROM zdd_maker_account a " +
			" JOIN zdd_maker m on a.userid = m.userid " +
			" WHERE m.state != 4 " +
			" AND m.phone != '' " +
			" AND LENGTH(phone) = 11 " +
			" AND a.identity_type = 1 " +
			" AND LENGTH(a.idno) = 18 " +
			" AND SUBSTRING(a.idno,11,4) = DATE_FORMAT(now(),'%m%d')")
	List<MakerBriefDto> getMakerBirth();

	//获取今日注册周年纪念日的创客
	@Select("SELECT YEAR(now()) - YEAR(createtime) as year,m.phone " +
			" FROM zdd_maker m" +
			" WHERE m.state != 4 " +
			" AND m.phone != '' " +
			" AND LENGTH(phone) = 11 " +
			" AND DATE_FORMAT(createtime,'%m%d') = DATE_FORMAT(now(),'%m%d')")
	List<MakerBriefDto> getMakerAnniversary();

	@Select("SELECT GROUP_CONCAT(ma.userid) FROM zdd_maker_account ma " +
			" WHERE ma.idno in (#{idno},#{idno1},#{idno2}) ")
	String getUserIdsByIdno(String idno, String idno1, String idno2);
}
